﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CustomerDA : ICustomerDA
    {
        private string ADDRESS = "@ADDRESS";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_ZIP = "@B_ZIP";
        private string CITY = "@CITY";
        private string COMPANY = "@COMPANY";
        private string CONTACT1 = "@CONTACT1";
        private string CONTACT2 = "@CONTACT2";
        private string COUNTRY = "@COUNTRY";
        private string COUNTRY_CODE = "@COUNTRY_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string CUSTOMER_ID = "@CUSTOMER_ID";
        private string DESCRIPTION = "@DESCRIPTION";
        private string EMAIL1 = "@EMAIL1";
        private string EMAIL2 = "@EMAIL2";
        private string FAX1 = "@FAX1";
        private string FAX2 = "@FAX2";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string PHONE1 = "@PHONE1";
        private string PHONE2 = "@PHONE2";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_AUTHORIZATION_CUSTOMER_USERID = "SELECT COUNT(1) FROM @_@CUSTOMER C INNER JOIN SYSADM.USER_CUSTOMER UC ON C.CUSTOMER_ID = UC.CUSTOMER_ID AND UC.[USER_ID] = @USER_ID WHERE C.CUSTOMER_ID = @CUSTOMER_ID ";
        private string SQL_CHECK_CUSTOMER_ID_UNIQUE = " SELECT COUNT(1) FROM @_@CUSTOMER  WHERE CUSTOMER_ID = @CUSTOMER_ID ";
        private string SQL_DELETE_CUSTOMER = " DELETE FROM @_@CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID ";
        private string SQL_INSERT_CUSTOMER = " INSERT INTO @_@CUSTOMER ( WH_ID, CUSTOMER_ID, COMPANY, DESCRIPTION, VAT, CITY, STATE, ZIP, COUNTRY, COUNTRY_CODE, ADDRESS, CONTACT1, CONTACT2, PHONE1, PHONE2, FAX1, FAX2, EMAIL1, EMAIL2, B_CITY, B_STATE, B_ZIP, B_COUNTRY, B_COUNTRY_CODE, B_ADDRESS, B_CONTACT1, B_CONTACT2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_EMAIL1, B_EMAIL2, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @CUSTOMER_ID, @COMPANY, @DESCRIPTION, @VAT, @CITY, @STATE, @ZIP, @COUNTRY, @COUNTRY_CODE, @ADDRESS, @CONTACT1, @CONTACT2, @PHONE1, @PHONE2, @FAX1, @FAX2, @EMAIL1, @EMAIL2, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY, @B_COUNTRY_CODE, @B_ADDRESS, @B_CONTACT1, @B_CONTACT2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_EMAIL1, @B_EMAIL2, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_CUSTOMER = " SELECT  C.ROWID, C.WH_ID, C.CUSTOMER_ID, C.COMPANY, C.DESCRIPTION, C.VAT, C.CITY, C.STATE, C.ZIP, C.COUNTRY, C.COUNTRY_CODE, C.ADDRESS, C.CONTACT1, C.CONTACT2, C.PHONE1, C.PHONE2, C.FAX1, C.FAX2, C.EMAIL1, C.EMAIL2, C.B_CITY, C.B_STATE, C.B_ZIP, C.B_COUNTRY, C.B_COUNTRY_CODE, C.B_ADDRESS, C.B_CONTACT1, C.B_CONTACT2, C.B_PHONE1, C.B_PHONE2, C.B_FAX1, C.B_FAX2, C.B_EMAIL1, C.B_EMAIL2, C.UDF1, C.UDF2, C.UDF3, C.UDF4, C.UDF5, C.UDF6, C.UDF7, C.UDF8, C.UDF9, C.UDF10, C.REMARK, C.IS_ACTIVE, C.TS, C.CREATED_BY, C.CREATED_DATE, C.UPDATED_BY, C.UPDATED_DATE  FROM  @_@CUSTOMER C  INNER JOIN SYSADM.USER_CUSTOMER UC ON C.CUSTOMER_ID = UC.CUSTOMER_ID AND UC.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_CUSTOMER = " UPDATE @_@CUSTOMER SET WH_ID = @WH_ID, COMPANY = @COMPANY, DESCRIPTION = @DESCRIPTION, VAT = @VAT, CITY = @CITY, STATE = @STATE, ZIP = @ZIP, COUNTRY = @COUNTRY, COUNTRY_CODE = @COUNTRY_CODE, ADDRESS = @ADDRESS, CONTACT1 = @CONTACT1, CONTACT2 = @CONTACT2, PHONE1 = @PHONE1, PHONE2 = @PHONE2, FAX1 = @FAX1, FAX2 = @FAX2, EMAIL1 = @EMAIL1, EMAIL2 = @EMAIL2, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY = @B_COUNTRY, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_ADDRESS = @B_ADDRESS, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE CUSTOMER_ID = @CUSTOMER_ID  ";
        private string STATE = "@STATE";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VAT = "@VAT";
        private string WH_ID = "@WH_ID";
        private string ZIP = "@ZIP";

        public bool CheckAuthorizationCustomerIDUserID(string customerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_AUTHORIZATION_CUSTOMER_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckAuthorizationCustomerIDUserID(DataBase dataBase, DbTransaction tran, string customerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_AUTHORIZATION_CUSTOMER_USERID.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCustomerIDUnique(string customerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_CUSTOMER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCustomerIDUnique(DataBase dataBase, DbTransaction tran, string customerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_CUSTOMER_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteCustomer(List<string> customerID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < customerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteCustomer(DataBase dataBase, DbTransaction tran, List<string> customerID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < customerID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllCustomer(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllCustomer(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public CustomerInfo GetCustomerByID(string customerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID) + " WHERE C.CUSTOMER_ID = @CUSTOMER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            CustomerInfo customerInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    customerInfo = new CustomerInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return customerInfo;
        }

        public CustomerInfo GetCustomerByID(DataBase dataBase, DbTransaction tran, string customerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID) + " WHERE C.CUSTOMER_ID = @CUSTOMER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            CustomerInfo customerInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                customerInfo = new CustomerInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return customerInfo;
        }

        public DataSet GetCustomerByQueryList(List<string> sqlWhere, CustomerQueryEntity customerQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!customerQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, customerQuery.CurrentPage, customerQuery.PageSize, customerQuery.SortField, customerQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetCustomerByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, CustomerQueryEntity customerQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CUSTOMER.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!customerQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, customerQuery.CurrentPage, customerQuery.PageSize, customerQuery.SortField, customerQuery.SortDirection).Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public CustomerInfo GetCustomerWebServiceByID(DataBase dataBase, DbTransaction tran, string customerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = " SELECT  C.ROWID, C.WH_ID, C.CUSTOMER_ID, C.COMPANY, C.DESCRIPTION, C.VAT, C.CITY, C.STATE, C.ZIP, C.COUNTRY, C.COUNTRY_CODE, C.ADDRESS, C.CONTACT1, C.CONTACT2, C.PHONE1, C.PHONE2, C.FAX1, C.FAX2, C.EMAIL1, C.EMAIL2, C.B_CITY, C.B_STATE, C.B_ZIP, C.B_COUNTRY, C.B_COUNTRY_CODE, C.B_ADDRESS, C.B_CONTACT1, C.B_CONTACT2, C.B_PHONE1, C.B_PHONE2, C.B_FAX1, C.B_FAX2, C.B_EMAIL1, C.B_EMAIL2, C.UDF1, C.UDF2, C.UDF3, C.UDF4, C.UDF5, C.UDF6, C.UDF7, C.UDF8, C.UDF9, C.UDF10, C.REMARK, C.IS_ACTIVE, C.TS, C.CREATED_BY, C.CREATED_DATE, C.UPDATED_BY, C.UPDATED_DATE  FROM  @_@CUSTOMER C ".Replace("@_@", whLoginID) + " WHERE C.CUSTOMER_ID = @CUSTOMER_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CUSTOMER_ID", customerID) };
            CustomerInfo customerInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                customerInfo = new CustomerInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["CUSTOMER_ID"].ToString(), reader["COMPANY"].ToString(), reader["DESCRIPTION"].ToString(), reader["VAT"].ToString(), reader["CITY"].ToString(), reader["STATE"].ToString(), reader["ZIP"].ToString(), reader["COUNTRY"].ToString(), reader["COUNTRY_CODE"].ToString(), reader["ADDRESS"].ToString(), reader["CONTACT1"].ToString(), reader["CONTACT2"].ToString(), reader["PHONE1"].ToString(), reader["PHONE2"].ToString(), reader["FAX1"].ToString(), reader["FAX2"].ToString(), reader["EMAIL1"].ToString(), reader["EMAIL2"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return customerInfo;
        }

        public int InsertCustomer(CustomerInfo customerInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    customerInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Customer_Parameters(customerInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertCustomer(DataBase dataBase, DbTransaction tran, CustomerInfo customerInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    customerInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Customer_Parameters(customerInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Customer_Parameters(CustomerInfo customerInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.CUSTOMER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.DESCRIPTION, SqlDbType.VarChar, 50), new SqlParameter(this.VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CITY, SqlDbType.VarChar, 50), new SqlParameter(this.STATE, SqlDbType.VarChar, 20), new SqlParameter(this.ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.FAX1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), 
                new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x7d0), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(customerInfo.WhID))
            {
                paramArray[0].Value = customerInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = customerInfo.CustomerID;
            if (!string.IsNullOrEmpty(customerInfo.Company))
            {
                paramArray[2].Value = customerInfo.Company;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Description))
            {
                paramArray[3].Value = customerInfo.Description;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Vat))
            {
                paramArray[4].Value = customerInfo.Vat;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.City))
            {
                paramArray[5].Value = customerInfo.City;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.State))
            {
                paramArray[6].Value = customerInfo.State;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Zip))
            {
                paramArray[7].Value = customerInfo.Zip;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Country))
            {
                paramArray[8].Value = customerInfo.Country;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.CountryCode))
            {
                paramArray[9].Value = customerInfo.CountryCode;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Address))
            {
                paramArray[10].Value = customerInfo.Address;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Contact1))
            {
                paramArray[11].Value = customerInfo.Contact1;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Contact2))
            {
                paramArray[12].Value = customerInfo.Contact2;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Phone1))
            {
                paramArray[13].Value = customerInfo.Phone1;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Phone2))
            {
                paramArray[14].Value = customerInfo.Phone2;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Fax1))
            {
                paramArray[15].Value = customerInfo.Fax1;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Fax2))
            {
                paramArray[0x10].Value = customerInfo.Fax2;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Email1))
            {
                paramArray[0x11].Value = customerInfo.Email1;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Email2))
            {
                paramArray[0x12].Value = customerInfo.Email2;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BCity))
            {
                paramArray[0x13].Value = customerInfo.BCity;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BState))
            {
                paramArray[20].Value = customerInfo.BState;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BZip))
            {
                paramArray[0x15].Value = customerInfo.BZip;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BCountry))
            {
                paramArray[0x16].Value = customerInfo.BCountry;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BCountryCode))
            {
                paramArray[0x17].Value = customerInfo.BCountryCode;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BAddress))
            {
                paramArray[0x18].Value = customerInfo.BAddress;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BContact1))
            {
                paramArray[0x19].Value = customerInfo.BContact1;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BContact2))
            {
                paramArray[0x1a].Value = customerInfo.BContact2;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BPhone1))
            {
                paramArray[0x1b].Value = customerInfo.BPhone1;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BPhone2))
            {
                paramArray[0x1c].Value = customerInfo.BPhone2;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BFax1))
            {
                paramArray[0x1d].Value = customerInfo.BFax1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BFax2))
            {
                paramArray[30].Value = customerInfo.BFax2;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BEmail1))
            {
                paramArray[0x1f].Value = customerInfo.BEmail1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.BEmail2))
            {
                paramArray[0x20].Value = customerInfo.BEmail2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf1))
            {
                paramArray[0x21].Value = customerInfo.Udf1;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf2))
            {
                paramArray[0x22].Value = customerInfo.Udf2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf3))
            {
                paramArray[0x23].Value = customerInfo.Udf3;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf4))
            {
                paramArray[0x24].Value = customerInfo.Udf4;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf5))
            {
                paramArray[0x25].Value = customerInfo.Udf5;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf6))
            {
                paramArray[0x26].Value = customerInfo.Udf6;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf7))
            {
                paramArray[0x27].Value = customerInfo.Udf7;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf8))
            {
                paramArray[40].Value = customerInfo.Udf8;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf9))
            {
                paramArray[0x29].Value = customerInfo.Udf9;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Udf10))
            {
                paramArray[0x2a].Value = customerInfo.Udf10;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(customerInfo.Remark))
            {
                paramArray[0x2b].Value = customerInfo.Remark;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            paramArray[0x2c].Value = customerInfo.IsActive;
            paramArray[0x2d].Value = customerInfo.CreatedBy;
            paramArray[0x2e].Value = customerInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateCustomer(CustomerInfo customerInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    customerInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Customer_Parameters(customerInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateCustomer(DataBase dataBase, DbTransaction tran, CustomerInfo customerInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    customerInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Customer_Parameters(customerInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CUSTOMER.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

